SQL Tutorial: What is SQL?

    A database is a well-structured collection of data that stores and manipulates data electronically. The most notable advantage of a database is that it makes the storage, retrieval, and management of vast volumes of data effortless.

    Moreover, different types of databases are available based on how they store data. A relational database is a type of database that stores data in the form of tables, i.e., rows and columns. A domain-specific language named SQL is used to query data stored in relational databases. SQL stands for Structured Query Language, designed to manage data stored in relational databases.

    SQL Tutorial

    This SQL tutorial is intended for absolute beginners with little to no knowledge of this language. We have covered all the basic concepts of SQL that every developer must know while working with relational databases. Let us start with an introduction to Structured Query Language.

    What is SQL?

    Structured Query Language is a domain-specific language specifically designed for managing data in relational database management systems (RDBMS). In simple terms, it is a language that facilitates communication between end-users or applications and databases.

    Generally, we pronounce ‘SQL’ as ‘ess-qew-ell’ or sequel. It is a de-facto standard language for all relational database management systems (RDBMS), such as MySQL, SQL Server, Oracle, Sybase, PostgreSQL, and Ingres.

    In addition, it is widely used for stream processing in a relational data stream management system (RDSMS). It can perform multiple operations, along with the maintenance and optimization of relational databases.

    Basically, the structured query language is based on tuple relational calculus and relational algebra. It was one of the first languages that leveraged E.F. Codd’s relational model. Though this language does not completely adhere to the relational model of E.F. Codd, it is one of the most widespread languages for relational databases.

    Furthermore, SQL became a standard of the American National Standards Institute (ANSI) and the International Organization for Standardization (ISO) in 1986 and 1987, respectively.

    History

    In the early 1970s, Donald D. Chamberlin and Raymond F. Boyce initially developed SQL at IBM after learning E.F. Codd’s relational model. Initially, they named it SEQUEL, which stood for Structured English Query Language. Both the developers designed this language, particularly for retrieving and manipulating data in IBM’s quasi relational database management system, System R.

    Even before SEQUEL, Boyce, and Chamberlin designed SQUARE (Specifying Queries in A Relational Environment) as a relational database language. However, it was difficult to use because of the subscript/superscript notation.

    A few years after the release of SEQUEL, the developers renamed it to SQL because ‘SEQUEL’ was the trademark of the Hawker Siddeley Dynamics Engineering Limited company. Later, SQL became an acronym for Structured Query Language.

    Syntax

    The structured query language consists of various language elements that are as follows:

    • Keywords: Keywords are pre-defined words in the structured query language. They can be either reserved or non-reserved.
    • Identifiers: Identifiers are the names of database objects, such as tables, views, columns, database schema, etc.
    • Clauses: Clauses are part of statements or queries.
    • Expressions: Expressions produce scalar values or tables consisting of data in the form of rows and columns.
    • Predicates: Predicates specify conditions whose value evaluates to the Boolean truth values or three-valued logic (true/false/unknown).
    • Queries: Queries help users to retrieve data from a database based on specific conditions.
    • Statements: A statement is any set of instructions consisting of identifiers, variables, parameters, names, data types, and reserved words.

    Advantages and Disadvantages of SQL

    Advantages

    The following are the remarkable benefits of the Structured Query Language:

    • Quick Query Processing: With the Structured Query Language, you can quickly retrieve large amounts of data without any hassle. Also, it lets you insert, delete, and update data in a database within a fraction of the time.
    • Easy To Learn: This query language is simple and easy to learn. Unlike other programming languages, there is no need to remember complex syntax. Instead, this language uses simple English constructs that are easy to remember.
    • Large User Community: Structured Query Language is one of the oldest and most popular query languages and has a large user community. Also, the community has many experts willing to share the best practices for using this language.
    • Portable: It is a portable language since we can use it on PCs, servers, laptops, and even some mobile devices.
    • Multiple Data Views: This language can create multiple views for a single database.

    Disadvantages

    Here are some of the major downsides of the Structured Query Language:

    • Complex Interface: This query language has a pretty complex interface. As a result, some users may find it difficult to use while working with databases.
    • High Cost: Some versions of this language are quite expensive. Therefore, some people may not be able to afford it.
    • Partial Control: This language provides partial control over a database because of the hidden business rules.

    Types of SQL Statements

    There are five different categories of SQL statements, namely data definition language (DDL), data manipulation language (DML), data control language (DCL), data query language (DQL), and Transaction Control Language (TCL).

    Let us discuss each of these kinds of SQL statements in detail below.

    1. Data Definition Language (DDL)

    Data definition language (DDL) statements define the structure and schema of a database or table. There are four different types of DDL statements, mentioned as follows:

    • CREATE

    This statement creates a new database or table. Also, it helps us in creating other database objects, such as views and stored procedures.

    Syntax: The following is the syntax for creating a new database: 

    CREATE DATABASE database_name;

    The following is the syntax for creating a new table in an existing database: 

    CREATE TABLE table_name (COLUMN_NAME1 DATATYPE, COLUMN_NAME2 DATATYPE, ...COLUMN_NAMEn DATATYPE);

    Here is the syntax for creating a view: 

    CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

    • ALTER

    This command enables us to alter or modify the structure of a table, i.e., we can add, delete, modify, and rename any database table column.

    Syntax:

    • Add Column

    You can add a new column to an existing database table using the following syntax: 

    ALTER TABLE table_name 
ADD COLUMN column_name datatype;
    • Drop Column

    If you want to delete any column from an existing database table, use the following syntax: 

    ALTER TABLE table_name
DROP COLUMN column_name;
    • Modify Column

    Suppose that you want to change the data type of a particular column in a table. The following syntax will help you to do so: 

    ALTER TABLE table_name
MODIFY COLUMN column_name datatype;
    • Rename Table or Column

    If you want to rename the name of a particular table, use the following syntax: 

    ALTER TABLE table_name 
RENAME TO NEW table_name;

    The following syntax will help you rename a column: 

    ALTER TABLE table_name
RENAME COLUMN old_column_name to new_column_name;

    • DROP

    The DROP statement removes the tables and databases from the relational database management systems.

    Syntax: Here is the basic syntax for deleting a table from a database: 

    DROP TABLE table_name;

    • TRUNCATE

    The TRUNCATE command frees up the space from a table by removing all the rows and columns.

    Syntax: 

    TRUNCATE TABLE table_name;

    2. Data Manipulation Language (DML)

    Data Manipulation Language (DML) statements enable us to insert, delete, and update data in a database. Three basic DML statements are as follows:

    • INSERT

    This statement lets you insert new data or records into a table. The following syntax enables you to insert multiple values into a particular table:

    Syntax: 

    INSERT INTO table_name (COL1, COL2, ...,COLn)
VALUES (VALUE1, VALUE2, ...,VALUEn);

    or 

    INSERT INTO table_name
VALUES (VALUE1, VALUE2, ...,VALUEn);

    • DELETE

    This statement deletes one or more records from the specified table.

    Syntax: 

    DELETE FROM table_name
WHERE condition;

    • UPDATE

    You can use the UPDATE statement to modify or update any existing values in a table.

    Syntax: 

    UPDATE table_name 
SET [COLUMN_NAME1 = VALUE1, COLUMN_NAME2 = VALUE2, …,COLUMN_NAMEn = VALUEn]
WHERE condition;

    3. Data Control Language (DCL)

    Data Control Language (DCL) statements allow you to control the data in a database. You can grant or revoke the rights of accessing a database by a particular user with DCL commands. There are two DCL commands, namely GRANT and REVOKE.

    • GRANT

    Using this command, you can give a particular user access privileges to a database.

    Syntax: 

    GRANT privilege_name
ON object_name
TO {user_name |PUBLIC |role_name}
[WITH GRANT OPTION];

    • REVOKE

    The REVOKE statement takes away the previously granted permissions from a specific user.

    Syntax: 

    REVOKE privilege_name
ON object_name
FROM {user_name |PUBLIC |role_name}

    4. Transaction Control Language (TCL)

    Transaction Control Language (TCL) statements help you manage transactions in a database. They manage all the changes you make to a database using DML commands.

    The following are the three TCL statements in SQL:

    • COMMIT

    This statement allows you to save all the transactions you perform in a database permanently.

    Syntax: 

    COMMIT;

    • ROLLBACK

    The ROLLBACK statement lets you undo transactions not saved to a database. In other words, it helps you restore the database from the last committed state.

    Syntax: 

    ROLLBACK;

    • SAVEPOINT

    When you want to temporarily store a transaction in a database to roll back to that point as and when required, you can use the SAVEPOINT statement.

    Syntax: 

    SAVEPOINT savepoint_name;

    5. Data Query Language (DQL)

    There is only one command in DQL called SELECT, which is used to retrieve data from a database.

    Syntax: The following syntax retrieves the entire data from the specified table: 

    SELECT * FROM table_name;

    If you want to retrieve specific records from a table, use the following syntax: 

    SELECT expressions
FROM table_name
WHERE conditions;

    SQL Query Evaluation

    Whenever we provide a SQL query to any relational database, it immediately responds to us with the required data. This seems so easy, right? But have you ever wondered how the database does it?

    Well, it uses a SQL query execution engine that is present on the top of the database, executes all the user queries against the data in that database, and provides the desired output.

    Let us understand the evaluation of a SQL query which consists of four different phases, as explained below:

    • Parser/Translator

    When we submit a query to a database, the parser is responsible for verifying its syntax. It checks whether the query is syntactically correct or not. Also, sometimes, it checks the semantics of the query.

    However, the SQL query we provide is basically a high-level language. As a result, the translator converts it into a low-level language or machine language. More specifically, it converts the query into a relational algebraic expression.

    • Optimizer

    Once the translator converts the query into a relational algebraic expression, multiple query execution plans are created to run the query. However, choosing the best and optimal plan for executing the query is essential. This is where the role of the optimizer comes into play. It determines the best and low-cost plan for query execution.

    • Execution Plan

    During the phase of the execution plan, the database decides the order of execution of the instructions the query contains. For instance, consider the following query: 

    SELECT * FROM Employee
WHERE emp_age>35;

    In the above query, the database will first execute the ‘FROM’ statement, later ‘WHERE’, and finally the ‘SELECT’ statement. So, the execution order becomes 

    FROM -> WHERE -> SELECT

    • Query Execution Engine

    Finally, the query execution engine takes the query evaluation plan from the database, executes it by retrieving data from the database, and responds to you with the desired data. In short, the query execution engine interprets the SQL commands , accesses the relational database, and returns the requested data.

    Why is SQL Popular?

    Here are some reasons why SQL is still a popular query language:

    1. Universal Language

    SQL is a universal language for all relational database management systems (RDBMS). This means that for all relational databases, you can use the basic SQL commands, including CREATE, INSERT, DELETE, and UPDATE. Though each DBMS has a different query language, for instance, SQL Server leverages TL/SQL, and Oracle uses PL/SQL, the basic SQL queries for all RDBMS will be the same. Hence, SQL is a universal language.

    2. Easy to Learn and Open-Source

    SQL is open-source; hence, it has a huge community of developers. In addition, it is easy to learn since it uses English-like sentences in queries, such as CREATE, DELETE, INSERT, and UPDATE. The column and table names are also lengthy, so anyone can understand what kind of data they contain.

    Due to its open-source nature and ease of learning, SQL is among the most popular query languages.

    3. Manage Millions of Rows

    Many of us might have worked with traditional spreadsheets to manage data. However, those spreadsheets can handle only small or medium-sized data sets. But for large datasets to be stored and managed efficiently, SQL is an ideal choice. It can retrieve huge heaps of data, whether they contain 1,000 or 10 million records.

    Relational databases store and manage millions of records without the fear of crashing, as might happen with spreadsheets when storing a huge amount of data in them. Also, with just a single SQL query, retrieving multiple records from one or more relational database tables is possible.

    What is SQL Used For? Top 7 Applications of SQL

    Let us now discuss some dynamic uses of SQL in today’s era.

    1. Relational Databases

    One of the most fundamental uses of SQL can be observed in relational databases. SQL is the only way to manage and manipulate data stored in all relational databases, including Oracle, SQL Server, MySQL, PostgreSQL, and MariaDB. With SQL, it is possible to perform various operations on relational databases, as follows:

    • Create and drop a database, view, table, or stored procedure .
    • Insert new data into a database.
    • Delete existing data from a database.
    • Modify the data stored in a database.
    • Execute various queries on a database.
    • Set permissions on tables, views, databases, and procedures.

    2. Basic Operations in Relational Databases

    To communicate with relational databases and perform various tasks and functions, SQL commands, such as DDL, DML, DCL, and DQL, come in handy.

    3. Transaction Control in Databases

    Another primary use of SQL in relational databases is to manage and control the transactions taking place in a database in order to maintain a database in compliance with the ACID properties . ACID stands for Atomicity, Consistency, Isolation, and Durability.

    For controlling transactions in relational databases, SQL provides three commands: COMMIT, ROLLBACK, and SAVEPOINT, which are transaction control commands. More importantly, you can use these commands only with the DML commands because DDL, DCL, and DQL commands are auto-committed.

    4. Union and Join

    With SQL union, combining the result sets of two or more different SELECT statements is possible. It removes all the duplicate rows from the combined result and displays a single distinct result set.

    On the other hand, SQL joins are used to retrieve rows from multiple tables with a related column between them. In simple terms, SQL join lets you combine the rows of two or more tables by leveraging the common values in both.

    5. Websites

    Websites that deal with a vast amount of data associated with users, products, and services definitely use databases. To manage that data, they use SQL as a query language. Each website requires its own database to store its users' information and manage that data. Using SQL is the standard way.

    One such type of website that deals with a large amount of user data is IRCTC. It stores user data in its database and manages it using SQL. IRCTC and all other booking websites leverage SQL to manage the data stored in their databases.

    6. Data Science and Analytics

    The data science and analytics processes rely on enormous amounts of data to extract hidden trends and patterns and derive meaningful insights. Many organizations still leverage relational databases to store humungous data sets, and data scientists and analysts use simple SQL queries to manage those data sets.

    Data filtering is one of the most popular uses of SQL in data science and data analytics. To set a criterion to filter the data from a database, it offers a "WHERE" clause. Apart from this, SQL lets you perform various operations with data sets, such as slicing, aggregation, indexing, etc.

    7. Machine Learning

    Like data scientists and analysts, machine learning engineers also deal with tremendous data sets to train and build accurate machine learning models. The best example of the use of SQL in machine learning is BigQuery ML. It is a toolset that helps ML engineers create and implement ML models using standard SQL queries.

    Job Roles That Need SQL Expertise

    The following are some job roles that need you to possess a profound knowledge of SQL:

    • Data Scientist

    A data scientist is an IT specialist who exclusively extracts massive amounts of data from diverse sources. They then examine the data and produce insightful findings that assist organizations in making more useful decisions. Data scientists use SQL to work with the data and databases because they are central to their duties and responsibilities.

    If you aspire to become a data scientist but have no idea where to kick-start, you can check out here .

    • SEO Analyst

    An SEO Analyst’s roles and responsibilities revolve around analyzing data and optimizing websites’ content to boost organic traffic. As SEO analysts need to work with heaps of data, most of them leverage databases rather than Excel documents to manage that data. They definitely require SQL to manage data when they work with databases.

    • Software Engineer

    Software engineers are IT professionals whose roles and responsibilities involve designing, developing, deploying, and maintaining software applications. Along with the knowledge of programming languages, they need to possess an in-depth understanding of SQL because it is a standard language for relational databases. They may come across various projects that require immense use of relational databases, and therefore, having SQL skills will help them manage those projects.

    • Business Analyst

    Business analysts are professionals whose roles and responsibilities involve analyzing business operations, products, systems, and services to improve the current processes and assisting them in making more informed decisions through insights. As their role is tied to data, they need to rely heavily on relational databases. So, having SQL expertise is essential for business analysts.

    Next Tutorial